package com.service.db.impl;

import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import com.service.bean.alarm.AlarmRule;
import com.service.bean.alarm.HookArmAlarm;
import com.service.bean.area.Area;
import com.service.bean.area.LineArea;
import com.service.bean.area.PolygonArea;
import com.service.bean.area.Road;
import com.service.bean.company.Company;
import com.service.bean.device.Device;
import com.service.bean.device.HookArmCase;
import com.service.bean.device.Sensor;
import com.service.bean.device.Vehicle;
import com.service.bean.duty.VehicleDuty;
import com.service.bean.oil.OilBean;
import com.service.bean.oil.OilBox;
import com.service.bean.oil.OilBoxStandard;
import com.service.bean.oil.OilCard;
import com.service.bean.point.GarbageCollectionPoint;
import com.service.bean.point.LabelPoint;
import com.service.bean.point.Point;
import com.service.bean.point.RoadCheckPoint;
import com.service.bean.point.RoadPoint;
import com.service.bean.signal.GpsHookArmCaseSignal;
import com.service.bean.signal.GpsSignal;
import com.service.bean.signal.GpsSignalWorkSpeed;
import com.service.cache.CacheManager;
import com.service.constant.PointType;
import com.service.db.DbConnectionManager;
import com.service.db.PooledConnection;
import com.service.other.Logger;
import com.service.tools.Tools;

public class DbCache {
	
	/**
	 * 根据Gps编号获取车辆及相关信息
	 * @param deviceNo
	 * @return
	 */
	public Vehicle initVehicle(String deviceNo){
		Vehicle vehicle = getVehicle(deviceNo);
		if(null != vehicle){
			Area area = null;
			List<LabelPoint> wateringAddPoints = null;
			List<LabelPoint> dumpPoints = null;
	        List<LabelPoint> dischargePoints = null;
	        List<LabelPoint> collectionPoints = null;
	        List<GarbageCollectionPoint> garbagePoints = null;
			long x = System.currentTimeMillis();
			Vehicle vehicleForRef =CacheManager.instance().vehicleMap.get(deviceNo);
			vehicle.oilBox = getOilBox(vehicle.equipmentId,vehicle.company);
			if(null == vehicleForRef || null == vehicleForRef.lastGps){
				vehicle.lastGps = getLastGps(vehicle.equipmentId,vehicle.company);
			} else {
				vehicle.lastList = vehicleForRef.lastList;
				vehicle.lastGps = vehicleForRef.lastGps;
			}
			vehicle.sensorMap = getSensorMap(vehicle.equipmentId,vehicle.company);
			vehicle.alarmRuleMap = getAlarmRule(vehicle.equipmentId,vehicle.company);
			vehicle.areaMap = getAreaMap(vehicle.equipmentId,vehicle.company);
			vehicle.normalOilValue = getEquipmentType(vehicle.equipmentCategoryCode);
			vehicle.oilCard = getOilCardInfo(vehicle.device.deviceNo,vehicle.company);
			if(null == vehicleForRef || null == vehicleForRef.oilBean){
				vehicle.oilBean = new OilBean();
			} else {
				vehicle.oilBean = vehicleForRef.oilBean;
			}
			vehicle.fuelPrice = getFuelPrice(vehicle.device.deviceNo,vehicle.oilBox,vehicle.company);
			vehicle.duty = getVehicleDuty(vehicle, vehicle.company);
			Collection<Area> areas = vehicle.duty == null || vehicle.duty.areas == null || vehicle.duty.areas.size() == 0 ? vehicle.areaMap.values() : vehicle.duty.areas.values();
			for(Iterator<Area> it = areas.iterator(); it.hasNext();) {
				area = it.next();
				wateringAddPoints = getLabelPoints(vehicle, PointType.WATERING_ADD_POINT.type, area);
				if(wateringAddPoints != null && wateringAddPoints.size() != 0)
					vehicle.wateringAddPoints.put(area.id, wateringAddPoints);
				dumpPoints = getLabelPoints(vehicle, PointType.DUMP_POINT.type, area);
				if(dumpPoints != null && dumpPoints.size() != 0)
					vehicle.dumpPoints.put(area.id, dumpPoints);
				dischargePoints = getLabelPoints(vehicle, PointType.DISCHARGE_POINT.type, area);
				if(dischargePoints != null && dischargePoints.size() != 0)
					vehicle.dischargePoints.put(area.id, dischargePoints);
				collectionPoints = getLabelPoints(vehicle, PointType.COLLECTION_POINT.type, area);
				if(collectionPoints != null && collectionPoints.size() != 0)
					vehicle.collectionPoints.put(area.id, collectionPoints);
				garbagePoints = getGarbageCollectionPoints(vehicle, vehicle.company);
				if(garbagePoints != null && garbagePoints.size() != 0)
					vehicle.garbagePoints.put(area.id, garbagePoints);
			}
			System.out.println(vehicle.device.deviceNo + " " + (System.currentTimeMillis() - x));
		}
		return vehicle;
	}
	
	/**
	 * 根据Gps编号获取勾臂车箱体及相关信息
	 * @param deviceNo
	 * @return
	 */
	public HookArmCase initHookArmCase(String deviceNo) {
		HookArmCase box = getHookArmCase(deviceNo);
		//如果缓存中last信息不存在，从数据库中取最新一条
		HookArmCase boxrefresh = CacheManager.instance().getHookArmCase(box.device.deviceNo);
		if(null == boxrefresh || null == boxrefresh.lastGps){
			box.lastGps = getLastHookGps(box.caseId, box.company);
		} else {
			box.lastGps = boxrefresh.lastGps;
		}
		if(null == boxrefresh || null == boxrefresh.lastAlarm){
			box.lastAlarm = getLastHookGpsAlarm(box.caseId, box.company);
		} else {
			box.lastAlarm = boxrefresh.lastAlarm;
		}
//		if (CacheManager.instance().companyHookArmCasesMap.containsKey(box.company.code)) {
//			if (!CacheManager.instance().companyHookArmCasesMap.get(box.company.code).containsKey(box.device.deviceNo)) {
//				CacheManager.instance().companyHookArmCasesMap.get(box.company.code).put(box.device.deviceNo, box);
//			}
//		} else {
//			HashMap<String, HookArmCase> map = new HashMap<String, HookArmCase>();
//			map.put(box.device.deviceNo, box);
//			CacheManager.instance().companyHookArmCasesMap.put(box.company.code, map);
//		}
		return box;
	}
	
	/**
	 * 获取所有车辆及相关信息
	 */
	public void initVehicleMap(){
		List<Vehicle> vehicles = this.getVehicleMap();
		
//		HashMap<String,GpsSignal> mapLastGpsAll = new HashMap<String,GpsSignal>();
//		this.getLastGpsAll(mapLastGpsAll);

		Area area = null;
		List<LabelPoint> wateringAddPoints = null;
		List<LabelPoint> dumpPoints = null;
        List<LabelPoint> dischargePoints = null;
        List<LabelPoint> collectionPoints = null;
        List<GarbageCollectionPoint> garbagePoints = null;
		
		for(Vehicle vehicle :vehicles){
			//获取最新的一条gps缓存防止lastGps、oilBean被强制刷新
			Vehicle vehicleForRef =CacheManager.instance().vehicleMap.get(vehicle.device.deviceNo);
			long x = System.currentTimeMillis();
			vehicle.oilBox = getOilBox(vehicle.equipmentId,vehicle.company);
			if(null == vehicleForRef || null == vehicleForRef.lastGps){
				vehicle.lastGps = getLastGps(vehicle.equipmentId,vehicle.company);
			} else {
				vehicle.lastList = vehicleForRef.lastList;
				vehicle.lastGps = vehicleForRef.lastGps;
			}
			if(null == vehicleForRef || null == vehicleForRef.oilBean){
				vehicle.oilBean = new OilBean();
			} else {
				vehicle.oilBean = vehicleForRef.oilBean;
			}
			vehicle.sensorMap = getSensorMap(vehicle.equipmentId,vehicle.company);
			vehicle.alarmRuleMap = getAlarmRule(vehicle.equipmentId,vehicle.company);
			vehicle.areaMap = getAreaMap(vehicle.equipmentId,vehicle.company);
			vehicle.normalOilValue = getEquipmentType(vehicle.equipmentCategoryCode);
			vehicle.oilCard = getOilCardInfo(vehicle.device.deviceNo,vehicle.company);

			vehicle.fuelPrice = getFuelPrice(vehicle.device.deviceNo,vehicle.oilBox,vehicle.company);	
			vehicle.duty = getVehicleDuty(vehicle, vehicle.company);
			Collection<Area> areas = vehicle.duty == null || vehicle.duty.areas == null || vehicle.duty.areas.size() == 0 ? vehicle.areaMap.values() : vehicle.duty.areas.values();
			for(Iterator<Area> it = areas.iterator(); it.hasNext();) {
				area = it.next();
				wateringAddPoints = getLabelPoints(vehicle, PointType.WATERING_ADD_POINT.type, area);
				if(null != wateringAddPoints && 0 != wateringAddPoints.size())
					vehicle.wateringAddPoints.put(area.id, wateringAddPoints);
				dumpPoints = getLabelPoints(vehicle, PointType.DUMP_POINT.type, area);
				if(null != dumpPoints && 0 != dumpPoints.size())
					vehicle.dumpPoints.put(area.id, dumpPoints);
				dischargePoints = getLabelPoints(vehicle, PointType.DISCHARGE_POINT.type, area);
				if(null != dischargePoints && 0 != dischargePoints.size())
					vehicle.dischargePoints.put(area.id, dischargePoints);
				collectionPoints = getLabelPoints(vehicle, PointType.COLLECTION_POINT.type, area);
				if(null != collectionPoints && 0 != collectionPoints.size())
					vehicle.collectionPoints.put(area.id, collectionPoints);
				garbagePoints = getGarbageCollectionPoints(vehicle, vehicle.company);
				if(null != garbagePoints && 0 != garbagePoints.size())
					vehicle.garbagePoints.put(area.id, garbagePoints);
			}
			CacheManager.instance().vehicleMap.put(vehicle.device.deviceNo, vehicle);
			System.out.println(vehicle.company.code +"    " +vehicle.device.deviceNo + " " + (System.currentTimeMillis() - x));
		}
					
	}	

	/**
	 * 获取所有勾臂车箱体及相关信息
	 */
	public void initHookArmCaseMap() {
		List<HookArmCase> HookArmCaseList = getHookArmCaseMap();
		for (HookArmCase box : HookArmCaseList) {
			//如果缓存中last信息不存在，从数据库中取最新一条
			HookArmCase boxrefresh = CacheManager.instance().getHookArmCase(box.device.deviceNo);
			if(null == boxrefresh || null == boxrefresh.lastGps){
				box.lastGps = getLastHookGps(box.caseId, box.company);
			} else {
				box.lastGps = boxrefresh.lastGps;
			}
			if(null == boxrefresh || null == boxrefresh.lastAlarm){
				box.lastAlarm = getLastHookGpsAlarm(box.caseId, box.company);
			} else {
				box.lastAlarm = boxrefresh.lastAlarm;
			}
			box.garbageDisposalSitePoints = getLabelPoints(box);
//			if (CacheManager.instance().companyHookArmCasesMap.containsKey(box.company.code)) {
//				if (!CacheManager.instance().companyHookArmCasesMap.get(box.company.code).containsKey(box.device.deviceNo)) {
//					CacheManager.instance().companyHookArmCasesMap.get(box.company.code).put(box.device.deviceNo, box);
//				}
//			} else {
//				HashMap<String, HookArmCase> map = new HashMap<String, HookArmCase>();
//				map.put(box.device.deviceNo, box);
//				CacheManager.instance().companyHookArmCasesMap.put(box.company.code, map);
//			}
			CacheManager.instance().hookArmCaseMap.put(box.device.deviceNo, box);
		}
	}
	
	/**
	 * 根据GpsNo查询车辆
	 * @param deviceNo
	 * @return
	 */
	public Vehicle getVehicle(String deviceNo){
		Vehicle vehicle = null;
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		String sql = "SELECT EquipmentId,PlateNO,TotalMile,TotalMile2,TotalMileTime,TotalMileTime2,OilBulk,"
				+ "WhetherStrength,GPSNO,GPSKey,GPSVersion,CompanyCode,Place,CompanyName,"
				+ "JavaConnection,UserId,PassWord,EquipmentName,PumpDelivery,RotationalSpeed,PumpRated "
				+ "FROM rel_EquipGPS "
				+ "WHERE GPSNO = ? "
				+ "AND JavaConnection IS NOT NULL "
				+ "AND UserId  IS NOT NULL "
				+ "AND PassWord IS NOT NULL "
				+ "AND EquipmentId IS NOT NULL ";
		try{
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, deviceNo);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				vehicle = new Vehicle();
				vehicle.equipmentId = rs.getString("EquipmentId");
				vehicle.plateNO = rs.getString("PlateNO");
				vehicle.totalMile = rs.getDouble("TotalMile");
				vehicle.totalMile2 = rs.getDouble("TotalMile2");
				vehicle.totalMileTime = rs.getTimestamp("TotalMileTime");
				vehicle.totalMileTime2 = rs.getTimestamp("TotalMileTime2");
				//vehicle.oilBulk = rs.getDouble("OilBulk");
				vehicle.whetherStrength = rs.getInt("WhetherStrength");
				vehicle.device = new Device(rs.getString("GPSNO"),rs.getString("GPSKey"));
				vehicle.device.version = rs.getString("GPSVersion");
				vehicle.company = new Company(rs.getString("CompanyCode"));
				vehicle.company.place = rs.getString("Place");
				vehicle.company.companyName = rs.getString("CompanyName");
				vehicle.company.dataConnection = rs.getString("JavaConnection");
				vehicle.company.dbUser = rs.getString("UserId");
				vehicle.company.dbPwd = rs.getString("PassWord");
				vehicle.kindCode = rs.getString("EquipmentName");
				vehicle.pumpDelivery = rs.getDouble("PumpDelivery");
				vehicle.rotationalSpeed = rs.getDouble("RotationalSpeed");
				vehicle.pumpRated = rs.getDouble("PumpRated");
			}
		}catch(Exception e){
			Logger.err(e);
		}finally{
			try {
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
		return vehicle;
	}
	
	/**
	 * 根据GpsNo查询勾臂车箱体
	 * @param deviceNo
	 * @return
	 */
	public HookArmCase getHookArmCase(String deviceNo) {
		HookArmCase box = null;
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		String sql = "SELECT CaseId,CaseName,CaseType,CaseBulk,GPSNO,GPSKey,GPSVersion,"
				+ "CompanyCode,CompanyName,PLACE,JavaConnection,UserId,Password "
				+ "FROM rel_HookArmGPS WHERE GPSNO = ? "
				+ "AND JavaConnection IS NOT NULL AND UserId  IS NOT NULL "
				+ "AND PassWord IS NOT NULL AND CaseId IS NOT NULL ";
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, deviceNo);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				box = new HookArmCase();
				box.device = new Device(rs.getString("GPSNO"), rs.getString("GPSKey"));
				box.device.version = rs.getString("GPSVersion");
				box.caseId = String.valueOf(rs.getObject("CaseId"));
				box.caseName = rs.getString("CaseName");
				box.caseType = rs.getString("CaseType");
				box.caseBulk = rs.getString("CaseBulk");
				box.company = new Company(rs.getString("CompanyCode"));
				box.company.place = rs.getString("Place");
				box.company.companyName = rs.getString("CompanyName");
				box.company.dataConnection = rs.getString("JavaConnection");
				box.company.dbUser = rs.getString("UserId");
				box.company.dbPwd = rs.getString("Password");
			}
		} catch (Exception e) {
			Logger.err(e);
			box = null;
		} finally {
			try {
				if (null != rs)
					rs.close();
				if (null != pstmt)
					pstmt.close();
				if (null != con)
					con.close();
			} catch (Exception ex) {
				Logger.err(ex);
			}
		}
		return box;
	}
	
	/**
	 * 查询所有车辆，并更新到缓存中
	 */
	public List<Vehicle> getVehicleMap(){
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		Vehicle vehicle = null;
		List<Vehicle> vehicles = new ArrayList<Vehicle>();
		String sql = "SELECT EquipmentId,PlateNO,TotalMile,TotalMile2,TotalMileTime,TotalMileTime2,"
				+ "OilBulk,WhetherStrength,GPSNO,GPSKey,GPSVersion,CompanyCode,Place,"
				+ "CompanyName,JavaConnection,UserId,PassWord,EquipmentName,PumpDelivery,RotationalSpeed,PumpRated "
				+ "FROM rel_EquipGPS "
				+ "WHERE JavaConnection IS NOT NULL "
				+ "AND UserId  IS NOT NULL "
				+ "AND PassWord IS NOT NULL "
				+ "AND GPSNO IS NOT NULL "
				+ "AND EquipmentId IS NOT NULL";
		try{
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				vehicle = new Vehicle();
				vehicle.equipmentId = rs.getString("EquipmentId");
				vehicle.plateNO = rs.getString("PlateNO");
				vehicle.totalMile = rs.getDouble("TotalMile");
				vehicle.totalMile2 = rs.getDouble("TotalMile2");
				vehicle.totalMileTime = rs.getTimestamp("TotalMileTime");
				vehicle.totalMileTime2 = rs.getTimestamp("TotalMileTime2");
				vehicle.whetherStrength = rs.getInt("WhetherStrength");
				vehicle.device = new Device(rs.getString("GPSNO"),rs.getString("GPSKey"));
				vehicle.device.version = rs.getString("GPSVersion");
				vehicle.company = new Company(rs.getString("CompanyCode"));
				vehicle.company.place = rs.getString("Place");
				vehicle.company.companyName = rs.getString("CompanyName");
				vehicle.company.dataConnection = rs.getString("JavaConnection");
				vehicle.company.dbUser = rs.getString("UserId");
				vehicle.company.dbPwd = rs.getString("PassWord");
				vehicle.kindCode = rs.getString("EquipmentName");
				vehicle.pumpDelivery = rs.getDouble("PumpDelivery");
				vehicle.rotationalSpeed = rs.getDouble("RotationalSpeed");
				vehicle.pumpRated = rs.getDouble("PumpRated");
				vehicles.add(vehicle);
			}
			
		}catch(Exception e){
			Logger.err(e);
		}finally{
			try {
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
		return vehicles;
	}
	
	/**
	 * 查询所有勾臂车箱体，并更新到缓存中
	 * @return 
	 */
	public List<HookArmCase> getHookArmCaseMap() {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		HookArmCase box = null;
		List<HookArmCase> hookArmCaseList = new ArrayList<HookArmCase>();
		String sql = "SELECT CaseId,CaseName,CaseType,CaseBulk,GPSNO,GPSKey,GPSVersion,"
				+ "CompanyCode,CompanyName,PLACE,JavaConnection,UserId,Password "
				+ "FROM rel_HookArmGPS "
				+ "WHERE JavaConnection IS NOT NULL AND UserId  IS NOT NULL "
				+ "AND PassWord IS NOT NULL AND CaseId IS NOT NULL AND GPSNO IS NOT NULL";
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				box = new HookArmCase();
				box.device = new Device(rs.getString("GPSNO"), rs.getString("GPSKey"));
				box.device.version = rs.getString("GPSVersion");
				box.caseId = String.valueOf(rs.getObject("CaseId"));
				box.caseName = rs.getString("CaseName");
				box.caseType = rs.getString("CaseType");
				box.caseBulk = rs.getString("CaseBulk");
				box.company = new Company(rs.getString("CompanyCode"));
				box.company.place = rs.getString("Place");
				box.company.companyName = rs.getString("CompanyName");
				box.company.dataConnection = rs.getString("JavaConnection");
				box.company.dbUser = rs.getString("UserId");
				box.company.dbPwd = rs.getString("Password");
				hookArmCaseList.add(box);
			}
		} catch (Exception e) {
			Logger.err(e);
		} finally {
			try {
				if (null != rs)
					rs.close();
				if (null != pstmt)
					pstmt.close();
				if (null != con)
					con.close();
			} catch (Exception ex) {
				Logger.err(ex);
			}
		}
		return hookArmCaseList;
	}
	
	/**
	 * 根据车辆查找车辆油箱
	 * @param EquipmentId
	 * @param company
	 * @return
	 */
	public OilBox getOilBox(String EquipmentId,Company company){
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		OilBox oilBox = null;

		String sql = "SELECT ROB.OilBoxId,OilBoxName,FuelKind,FuelRadio,OilBoxBulk,ROB.OilBoxHight,"
				+ "ROB.OilBoxWidth,ROB.OilBoxLength "
				+ "FROM ref_OilBox ROB "
				+ "LEFT OUTER JOIN crc_Equipment CE ON ROB.OilBoxId = CE.OilBoxId "
				+ "WHERE CE.EquipmentId = ? "
				+ "AND ROB.Status = 'Active' AND CE.Status = 'Active'";
		try {
			con = DbConnectionManager.getConnection(company);

			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, EquipmentId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				oilBox = new OilBox();
				oilBox.oilBoxId = String.valueOf(rs.getObject("OilBoxId"));
				oilBox.oilBoxName = rs.getString("OilBoxName");
				oilBox.fuelKind = rs.getString("FuelKind");
				oilBox.fuelRadio = rs.getDouble("FuelRadio");
				oilBox.oilBoxBulk = rs.getDouble("OilBoxBulk");
				oilBox.oilBoxHight = rs.getDouble("OilBoxHight");
				oilBox.oilBoxWidth = rs.getDouble("OilBoxWidth");
				oilBox.oilBoxLength = rs.getDouble("OilBoxLength");
			}
			if (null != oilBox) {
				oilBox.standards = getOilBoxStandards(oilBox.oilBoxId,company);
			}
		} catch (SQLException e) {
			Logger.err(e);
		} finally{
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
		return oilBox;
	}
	
	/**
	 * 根据油箱查找油箱详细信息
	 * @param oilBoxId
	 * @param company
	 * @return
	 */
	public List<OilBoxStandard> getOilBoxStandards(String oilBoxId,Company company){
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		OilBoxStandard standard = null;
		List<OilBoxStandard> list = new ArrayList<OilBoxStandard>();

		String sql = "SELECT OilBoxId,OilPercent,OilValue,OilHeight FROM ref_OilBoxStandard "
				+ "WHERE OilBoxId = ? AND Status = 'Active' ORDER BY OilPercent ";
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, oilBoxId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				standard = new OilBoxStandard();
				standard.oilBoxId = String.valueOf(rs.getObject("OilBoxId"));
				standard.oilPercent = rs.getDouble("OilPercent");
				standard.oilValue = rs.getDouble("OilValue");
				standard.oilHeight = rs.getDouble("OilHeight");
				list.add(standard);
			}
		} catch (SQLException e) {
			Logger.err(e);
		} finally{
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
		return list;
	}

	
	/**
	 * 获取最后一条定位数据
	 * @param deviceNo
	 * @return
	 */
	public GpsSignal getLastGps(String EquipmentId,Company company){
		GpsSignal signal = null;
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		String strSQL = "select top 1 * from crc_GPSSignal where EquipmentId = ?  and GPSStatus = 1 "; 
		try{
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(strSQL);
			pstmt.setString(1, EquipmentId);

			long x = System.currentTimeMillis();
			rs = pstmt.executeQuery();
			System.out.println("getLastGps " + (System.currentTimeMillis() - x));
			if (rs.next()) {
				signal = new GpsSignal();
				Field[] field = signal.getClass().getFields();
				for(Field f : field){
					try {
						f.setAccessible(true);
						
						switch (f.getType().getName()) {
							case "java.lang.String":								
								if (rs.getString(f.getName()) != null){
									f.set(signal, rs.getString(f.getName()));
								}
								break;
							case "java.util.Date":
								if (rs.getTimestamp(f.getName()) != null){
									f.set(signal, rs.getTimestamp(f.getName()));
								}

								break;
							case "double":
								if (rs.getDouble(f.getName()) != 0){
									f.set(signal, rs.getDouble(f.getName()));
								}

								break;
							case "int":
								if (rs.getInt(f.getName()) != 0){
									f.set(signal, rs.getInt(f.getName()));
								}
								break;
							default:
								break;
						}

					}catch (Exception e) {
//						System.out.println(f.getName());
					}
				}
			}
		}catch(Exception e){
			Logger.err(e);
		}finally{
			try {
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
		return signal;
	}

	/**
	 * 获取箱体的最后一条gps数据
	 * @param EquipmentId
	 * @param company
	 */
	public GpsHookArmCaseSignal getLastHookGps(String caseId,Company company) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		GpsHookArmCaseSignal lastSignal = null;
		String sql = "SELECT TOP 1 SignalId,GPSNO,GPSTime,Lng,Lat,GPSStatus,EquipmentType,"
				+ "BaiduLat,BaiduLng,BaiduLocation,CompanyCode,CaseId,isGarbageFull,CaseName "
				+ "FROM crc_GPSHookArmCaseSignal "
				+ "WHERE CaseId = ? AND GPSStatus = 1 ORDER BY GPSTime DESC";
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, caseId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				lastSignal = new GpsHookArmCaseSignal();
				lastSignal.signalId = rs.getString("SignalId");
				lastSignal.gpsNo = rs.getString("GPSNO");
				lastSignal.gpsTime = rs.getTimestamp("GPSTime");
				lastSignal.lng = rs.getDouble("Lng");
				lastSignal.lat = rs.getDouble("Lat");
				lastSignal.gpsStatus = rs.getInt("GPSStatus");
				lastSignal.equipmentType = rs.getInt("EquipmentType");
				lastSignal.baiduLat = rs.getDouble("BaiduLat");
				lastSignal.baiduLng = rs.getDouble("BaiduLng");
				lastSignal.baiduLocation = rs.getString("BaiduLocation");
				lastSignal.companyCode = rs.getString("CompanyCode");
				lastSignal.caseId = String.valueOf(rs.getObject("CaseId"));
				lastSignal.isGarbageFull = rs.getInt("isGarbageFull");
				lastSignal.caseName = rs.getString("CaseName");
			}
		} catch (Exception e) {
			Logger.err(e);
			lastSignal = null;
		} finally {
			try {
				if (null != rs)
					rs.close();
				if (null != pstmt)
					pstmt.close();
				if (null != con)
					con.close();
			} catch (Exception ex) {
				Logger.err(ex);
			}
		}
		return lastSignal;
	}
	
	/**
	 * 获取最后一次箱体满溢报警的数据
	 * @param EquipmentId
	 * @param company
	 * @return
	 */
	public HookArmAlarm getLastHookGpsAlarm(String caseId,Company company) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		HookArmAlarm lastAlarm = null;
		String sql = "SELECT TOP 1 SignalId,GPSNO,CaseId,AlarmTime,CompanyCode,"
				+ "BaiduLng,BaiduLat,KeepTime,CaseName "
				+ "FROM crc_GPSHookArmCaseAlarm "
				+ "WHERE KeepTime = 0 AND CaseId = ? "
				+ "ORDER BY AlarmTime DESC";
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, caseId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				lastAlarm = new HookArmAlarm();
				lastAlarm.signalId = rs.getString("SignalId");
				lastAlarm.gpsNo = rs.getString("GPSNO");
				lastAlarm.caseId = String.valueOf(rs.getObject("CaseId"));
				lastAlarm.alarmTime = rs.getTimestamp("AlarmTime");
				lastAlarm.companyCode = rs.getString("CompanyCode");
				lastAlarm.baiduLng = rs.getDouble("BaiduLng");
				lastAlarm.baiduLat = rs.getDouble("BaiduLat");
				lastAlarm.keepTime = rs.getLong("KeepTime");
				lastAlarm.caseName = rs.getString("CaseName");
			}
		} catch (Exception e) {
			Logger.err(e);
			lastAlarm = null;
		} finally {
			try {
				if (null != rs)
					rs.close();
				if (null != pstmt)
					pstmt.close();
				if (null != con)
					con.close();
			} catch (Exception ex) {
				Logger.err(ex);
			}
		}
		return lastAlarm;
	}
	
	/**
	 * 获取车辆传感器
	 * @param EquipmentId
	 * @return
	 */
	public Map<String,Sensor> getSensorMap(String EquipmentId,Company company){
		Map<String,Sensor> sensorMap = new HashMap<String,Sensor>();
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		String sql = "SELECT EST.EquipmentId,RST.SensorCategory,RST.Remark,RST.OilMode,RST.CalMode,"
				+ "isnull(EST.Para1,0)Para1,isnull(EST.Para2,0)Para2,"
				+ "isnull(EST.Para3,0)Para3,isnull(EST.Para4,0)Para4 "
				+ "FROM crc_EquipmentSensorType EST "
				+ "LEFT JOIN ref_SensorType RST ON EST.SensorTypeId = RST.SensorTypeId "
				+ "WHERE EST.EquipmentId = ? "
				+ "AND EST.Status = 'Active' AND RST.Status = 'Active'";
		try{
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, EquipmentId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Sensor sensor = new Sensor();
				sensor.category = rs.getString("SensorCategory");
				sensor.remark = rs.getString("Remark");
				sensor.oilMode = rs.getString("OilMode");
				sensor.calMode = rs.getString("CalMode");
				sensor.para1 = rs.getDouble("Para1");
				sensor.para2 = rs.getDouble("Para1");
				sensor.para3 = rs.getDouble("Para1");
				sensor.para4 = rs.getDouble("Para1");
				sensorMap.put(sensor.category, sensor);
			}
		}catch(Exception e){
			Logger.err(e);
		}finally{
			try {
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
		return sensorMap;
	}

	/**
	 * 获取车辆报警
	 * @param equipmentId
	 * @param companyCode
	 * @return
	 */
	public Map<String,AlarmRule> getAlarmRule(String equipmentId,Company company){
		Map<String,AlarmRule> map = null;
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		String sql = "SELECT AlarmType,AlarmLevel,AlarmCategory,AlarmName,Value1,Value2,Value3,Value4 "
				+ "FROM [fGetAlarmRule](?,?)";
		try{
			map = new HashMap<String,AlarmRule>();
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, equipmentId);
			pstmt.setString(2, company.code);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				AlarmRule ar = new AlarmRule();
				ar.alarmType = rs.getString("alarmType");
				ar.alarmLevel = rs.getString("alarmLevel");
				ar.alarmCategory = rs.getString("alarmCategory");
				ar.alarmName = rs.getString("alarmName");
//				ar.areaId = String.valueOf(rs.getObject("AreaId"));
//				ar.areaType = rs.getString("AreaType");
				ar.value1 = rs.getString("value1");
				ar.value2 = rs.getString("value2");
				ar.value3 = rs.getString("value3");
				ar.value4 = rs.getString("value4");
//				ar.alarmTimeFrom = rs.getString("alarmTimeFrom");
//				ar.alarmTimeTo = rs.getString("alarmTimeTo");
//				if (map.containsKey(ar.alarmCategory)) {
//					map.get(ar.alarmCategory).areaId += ("," + ar.areaId);
//				} else {
//					map.put(ar.alarmCategory, ar);
//				}
				map.put(ar.alarmCategory, ar);
			}
		}catch(Exception e){
			Logger.err(e);
		}finally{
			try {
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
		return map;
	}
	
	/**
	 * 根据车辆编号获取车辆绑定的区域
	 * @param equipmentId
	 * @return
	 */
	public Map<String,Area> getAreaMap(String equipmentId,Company company){
		Map<String, Area> map = new LinkedHashMap<String,Area>();;
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		Area area = null;
		// 按照路线类型进行排序，01工作路线排在前面
		String sql = "SELECT CEA.EquipmentId,CEA.AreaId,CA.AreaName,CA.AreaType, "
				+ "CASE WHEN CA.AreaKind IS NULL THEN '99' "
				+ "     WHEN CA.AreaKind = '' THEN '99' "
				+ "     ELSE CA.AreaKind END AreaKind,CA.LineWidth "
				+ "FROM crc_EquipmentArea CEA "
				+ "LEFT JOIN crc_Area CA ON CEA.AreaId = CA.AreaId "
				+ "WHERE CEA.EquipmentId = ? "
				+ "AND CEA.Status = 'Active' "
				+ "AND CA.Status = 'Active' "
				+ "ORDER BY AreaKind";
		try{
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, equipmentId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				int type = rs.getInt("AreaType");
				switch(type){
					case 1:
						area = new PolygonArea();
						break;
					case 2:
						area = new LineArea();
						break;
				}
				area.id = rs.getString("AreaId");
				area.name = rs.getString("AreaName");
				area.type = type;
				area.kind = rs.getString("AreaKind");
				area.lineWidth = rs.getInt("LineWidth");
				// 获取组成区域的坐标点
				area.points = getAreaPoints(area.id,company);
				// 查找路段
				area.roads =  getRoad(area.id,company);
				map.put(area.id, area);
			}
		}catch(Exception e){
			Logger.err(e);
		}finally{
			try {
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
		return map;
	}
	
	/**
	 * 根据区域Id查找路段
	 * @param areaId
	 * @param company
	 * @return
	 */
	private Map<String, Road> getRoad(String areaId, Company company) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		Map<String, Road> roadList = new HashMap<String, Road>();
		String sql = "SELECT RoadId,RoadName,AreaId,ClearTimes,ClearWays FROM crc_Road "
				+ "WHERE AreaId = ? AND Status = 'Active'";
		try{
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, areaId);
			rs = pstmt.executeQuery();
			Road r = null;
			while (rs.next()) {
				r = new Road();
				r.roadId = rs.getString("roadId");
				r.roadName = rs.getString("RoadName");
				r.areaId = rs.getString("areaId");
				r.clearTimes = rs.getInt("clearTimes");
				r.clearWays = rs.getInt("clearWays");
				r.checkPoints = getRoadCheckPoints(r.roadId, company);
				r.roadPoints = getRoadPoints(r.roadId, company);
				roadList.put(r.roadId, r);
			}
		}catch(Exception e){
			Logger.err(e);
		}finally{
			try {
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
		return roadList;
	}

	/**
	 * 根据路段Id获取路段检测点
	 * @param roadId
	 * @param company
	 * @return
	 */
	private Map<String, RoadCheckPoint> getRoadCheckPoints(String roadId, Company company) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		Map<String, RoadCheckPoint> points = new HashMap<String, RoadCheckPoint>();
		String sql = "SELECT CheckPointId,RoadId,BaiduLat,BaiduLng FROM crc_RoadCheckPoints "
				+ "WHERE RoadId = ? AND Status = 'Active'";
		try{
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, roadId);
			rs = pstmt.executeQuery();
			RoadCheckPoint p = null;
			while (rs.next()) {
				p = new RoadCheckPoint();
				p.checkPointId = String.valueOf(rs.getObject("CheckPointId"));
				p.roadId = String.valueOf(rs.getObject("RoadId"));
				p.baiduLng = rs.getDouble("baidulng");
				p.baiduLat = rs.getDouble("baidulat");
				points.put(p.checkPointId, p);
			}
		}catch(Exception e){
			Logger.err(e);
		}finally{
			try {
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
		return points;
	}
	
	/**
	 * 根据路段Id获取路段点
	 * @param roadId
	 * @param company
	 * @return
	 */
	public Map<Integer, RoadPoint> getRoadPoints(String roadId, Company company) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		Map<Integer, RoadPoint> points = new HashMap<Integer, RoadPoint>();
		String sql = "SELECT Id,RoadId,Sequence,BaiduLat,BaiduLng FROM crc_RoadPoints "
				+ "WHERE RoadId = ? AND Status = 'Active' ORDER BY Sequence ASC";
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, roadId);
			rs = pstmt.executeQuery();
			RoadPoint point = null;
			while (rs.next()) {
				point = new RoadPoint();
				point.id = rs.getInt("Id");
				point.roadId = String.valueOf(rs.getObject("RoadId"));
				point.sequence = rs.getInt("Sequence");
				point.baiduLat = rs.getDouble("BaiduLat");
				point.baiduLng = rs.getDouble("BaiduLng");
				points.put(point.id, point);
			}
		} catch (Exception e) {
			Logger.err(e);
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (con != null)
					con.close();
			} catch (Exception ex) {
				Logger.err(ex);
			}
		}
		return points;
	}
	
	/**
	 * 获取组成区域的坐标点
	 * @param areaId
	 * @return List<Point>
	 */
	private List<Point> getAreaPoints(String areaId,Company company){
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		List<Point> points = null;
		String strSQL = "select Lat,Lng from crc_AreaPoints where AreaId = ? order by Sequence";
		try{
			points = new ArrayList<Point>();
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(strSQL);
			pstmt.setString(1, areaId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Point p = new Point();
				p.baiduLat = rs.getDouble("Lat");
				p.baiduLng = rs.getDouble("Lng");
				points.add(p);
			}
		}catch(Exception e){
			Logger.err(e);
		}finally{
			try {
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e) {
				Logger.err(e);
			}
		}
		return points;
	}

	/**
	 * 获取SignalId为最新的几条GpsSignalWorkSpeed对象集合
	 * @param equipmentId
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public List<GpsSignalWorkSpeed> getLastWorkSpeed(String equipmentId,Company company) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		GpsSignalWorkSpeed workSpeed = null;
		List<GpsSignalWorkSpeed> list = new ArrayList<GpsSignalWorkSpeed>();
		String sql = "select * from crc_GPSSignalWorkSpeed where (SignalMonth >= ? AND SignalMonth <= ?) and SignalId = (select MAX(SignalId) from crc_GPSSignalWorkSpeed where EquipmentId=? AND (SignalMonth >= ? AND SignalMonth <= ?))";
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, Tools.getPreviousMonth(new Date()).getMonth()+1);
			pstmt.setInt(2, new Date().getMonth()+1);
			pstmt.setString(3, equipmentId);
			pstmt.setInt(4, Tools.getPreviousMonth(new Date()).getMonth()+1);
			pstmt.setInt(5, new Date().getMonth()+1);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				workSpeed = new GpsSignalWorkSpeed();
				workSpeed.signalId = rs.getString("SignalId");
				workSpeed.gpsTime = rs.getDate("GPSTime");
				workSpeed.equipmentId = String.valueOf(rs.getObject("EquipmentId"));
				workSpeed.speed = rs.getInt("Speed");
				workSpeed.startTime = rs.getDate("StartTime");
				workSpeed.keepTime = rs.getLong("KeepTimer");
				workSpeed.maxSpeed = rs.getDouble("MaxSpeed");
				workSpeed.address = rs.getString("Address");
				list.add(workSpeed);
			}
		} catch (SQLException e) {
			Logger.err(e);
		} finally {
			try {
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			} catch (SQLException e1) {
				Logger.err(e1);
			}
		}
		return list;
	}

	/***
	 * 获取该车辆类型
	 * @return
	 */
	public static double getEquipmentType(String categoryCode){
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		double experience = 0;
		try {
			con = DbConnectionManager.getConnection();
			pstmt = con.prepareStatement("SELECT Experience FROM ref_EquipmentCategory WHERE CategoryCode=? AND Status = 'Active'");
			pstmt.setString(1, categoryCode);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				experience = rs.getDouble("Experience");
			}
		} catch (Exception e) {
			Logger.err(e);
		} finally{
			try{
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return experience ;
	}
	
	/**
	 * 获取油卡信息
	 * @param gpsNo
	 * @param company
	 * @return
	 */
	public OilCard getOilCardInfo(String gpsNo,Company company) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		OilCard oilCard = null;
		String sql = "SELECT CE.EquipmentId,CO.OilCardNO,CO.OilCardId,CO.LeftAmt,CEP.ProjectId "
				+ "FROM crc_Equipment CE "
				+ "LEFT OUTER JOIN crc_EquipmentProject CEP ON CE.EquipmentId = CEP.EquipmentId "
				+ "LEFT OUTER JOIN crc_OilCard CO ON CE.EquipmentId = CO.EquipmentId "
				+ "WHERE CE.GPSNO = ? "
				+ "AND CE.Status = 'Active' AND CEP.Status = 'Active' AND CO.Status = 'Active'";
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, gpsNo);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				oilCard = new OilCard();
				oilCard.oilCardId = rs.getString("OilCardId");				
				oilCard.oilCardNo = rs.getString("OilCardNO");
				oilCard.oilLeftAmt = rs.getDouble("LeftAmt");
				oilCard.projectId = rs.getString("ProjectId");
			}			
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return oilCard;
	}
	
	/**
	 * 查找油价
	 * @param gpsNo
	 * @param oilBox
	 * @param company
	 * @return
	 */
	public double getFuelPrice(String gpsNo,OilBox oilBox,Company company) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		double fuelPrice = 0.00;
		if(oilBox == null){
			//没有油箱,直接返回
			return fuelPrice;
		}
		
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement("select FuelPrice from ref_FuelConfig rf  where ? >= StartTime and ? < = EndTime  and rf.Status = 'Active' and rf.FuelKind = ?");
			pstmt.setTimestamp(1, new Timestamp(new Date().getTime()));
			pstmt.setTimestamp(2, new Timestamp(new Date().getTime()));
			pstmt.setString(3, oilBox.fuelKind);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				fuelPrice = rs.getDouble("FuelPrice");
			}
		} catch (Exception e) {
			Logger.err(e);
		}finally{
			try{
				if(rs!=null && !rs.isClosed()){
					rs.close();
				}
				if(pstmt!=null && !pstmt.isClosed()){
					pstmt.close();
				}
				if (con != null) {
					con.close();
				}
			}catch(Exception e){
				Logger.err(e);
			}
		}
		return fuelPrice;

	}
	
	/**
	 * 获取车辆的垃圾收集点坐标集合(仅用于垃圾称重!)
	 * @param vehicle
	 * @param company
	 * @return
	 */
	public List<GarbageCollectionPoint> getGarbageCollectionPoints(Vehicle vehicle, Company company) {
		PooledConnection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs= null;
		List<GarbageCollectionPoint> points = new LinkedList<GarbageCollectionPoint>();
		GarbageCollectionPoint point = null;
		String sql = "SELECT CCO.OwnerId,AreaId,OwnerName,Lat,Lng FROM crc_CollectionOwner CCO "
				+ "LEFT OUTER JOIN crc_EquipmentCollectionOwner CECO ON CCO.OwnerId = CECO.OwnerId "
				+ "WHERE EquipmentId = ? "
				+ "AND CCO.Status = 'Active' AND CECO.Status = 'Active'";
		try {
			con = DbConnectionManager.getConnection(company);
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, vehicle.equipmentId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				point = new GarbageCollectionPoint(/*rs.getDouble("Lat"), rs.getDouble("Lng")*/);
				point.baiduLat = rs.getDouble("Lat");
				point.baiduLng = rs.getDouble("Lng");
				point.ownerId = String.valueOf(rs.getObject("OwnerId"));
				point.areaId = String.valueOf(rs.getObject("AreaId"));
				point.ownerName = rs.getString("OwnerName");
				points.add(point);
			}
		} catch (Exception e) {
			Logger.err(e);
			points.clear();
		} finally {
			try {
				if (null != rs)
					rs.close();
				if (null != pstmt)
					pstmt.close();
				if (null != con)
					con.close();
			} catch (Exception e) {
				Logger.err(e);
			}
		}
		return points;
	}
	
	/**
	 * 获取车辆排班
	 * @param vehicle
	 * @param company
	 * @return
	 */
	public VehicleDuty getVehicleDuty(Vehicle vehicle, Company company) {
		PooledConnection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		VehicleDuty duty = null;
		String sql = "SELECT PlaneId,PlaneName,SchedId,SchedFullName,WorkStartTime,BeginFloat,WorkEndTime,EndFloat FROM [dbo].[fGetVehicleDuty] (?,?)";
		try {
			conn = DbConnectionManager.getConnection(company);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, vehicle.equipmentId);
			pstmt.setTimestamp(2, new Timestamp((new Date()).getTime()));
			rs = pstmt.executeQuery();
			while (rs.next()) {
				duty = new VehicleDuty();
				duty.equipmentId = vehicle.equipmentId;
				duty.planeId = String.valueOf(rs.getObject("PlaneId"));
				duty.planeName = rs.getString("PlaneName");
				duty.schedId = rs.getInt("SchedId");
				duty.schedName = rs.getString("SchedFullName");
				duty.workStartTime = rs.getTimestamp("WorkStartTime");
				duty.startFloatTime = rs.getInt("BeginFloat");
				duty.workEndTime = rs.getTimestamp("WorkEndTime");
				duty.endFloatTime = rs.getInt("EndFloat");
				duty.areas = getDutyArea(vehicle, duty);
			}
		} catch (Exception e) {
			Logger.err(e);
			duty = null;
		} finally {
			try {
				if (null != rs)
					rs.close();
				if (null != pstmt)
					pstmt.close();
				if (null != conn)
					conn.close();
			} catch (Exception ex) {
				Logger.err(ex);
			}
		}
		return duty;
	}
	
	/**
	 * 获取排班区域
	 * @param vehicle
	 * @param duty
	 * @return
	 */
	public Map<String, Area> getDutyArea(Vehicle vehicle, VehicleDuty duty) {
		PooledConnection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Map<String, Area> areas = new HashMap<String, Area>();
		String sql = "SELECT CEPA.AreaId,CEPA.RoadId,CA.AreaName,CA.AreaType,CA.LineWidth,"
				+ "CASE WHEN CA.AreaKind IS NULL THEN '99' "
				+ "WHEN CA.AreaKind = '' THEN '99' "
				+ "ELSE CA.AreaKind END AreaKind "
				+ "FROM crc_EquipmentPlaneArea CEPA LEFT OUTER JOIN crc_Area CA ON CEPA.AreaId = CA.AreaId "
				+ "WHERE PlaneId = ? AND CEPA.Status = 'Active' AND CA.Status = 'Active'";
		try {
			conn = DbConnectionManager.getConnection(vehicle.company);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, duty.planeId);
			rs = pstmt.executeQuery();
			Area area = null;
			for (; rs.next(); areas.put(area.id, area)) {
				int type = rs.getInt("AreaType");
				switch (type) {
					case 1:
						area = new PolygonArea();
						break;
					case 2:
						area = new LineArea();
						break;
				}
				area.id = rs.getString("AreaId");
				area.name = rs.getString("AreaName");
				area.type = type;
				area.kind = rs.getString("AreaKind");
				area.lineWidth = rs.getInt("LineWidth");
				area.points = getAreaPoints(area.id, vehicle.company);
				area.roads = getRoad(area.id, vehicle.company);
			}
		} catch (Exception e) {
			Logger.err(e);
		} finally {
			try {
				if (null != rs)
					rs.close();
				if (null != pstmt)
					pstmt.close();
				if (null != conn)
					conn.close();
			} catch (Exception ex) {
				Logger.err(ex);
			}
		}
		return areas;
	}
	
	/**
	 * 获取车辆标注点
	 * @param vehicle
	 * @param pointType 标注点类型(<code>PointType.java</code>)
	 * @param area
	 * @return
	 */
	public List<LabelPoint> getLabelPoints(Vehicle vehicle, String pointType, Area area) {
		PooledConnection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<LabelPoint> points = new ArrayList<LabelPoint>();
		String sql = "SELECT CollectionId,CollectionName,Lat,Lng,Seq "
				+ "FROM crc_EquipmentArea CEA LEFT OUTER JOIN crc_Collection CC ON CEA.AreaId = CC.AreaId "
				+ "WHERE EquipmentId = ? AND CollectionType = ? AND CEA.AreaId = ? "
				+ "AND CEA.Status = 'Active' AND CC.Status = 'Active' ORDER BY Seq";
		try {
			conn = DbConnectionManager.getConnection(vehicle.company);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, vehicle.equipmentId);
			pstmt.setString(2, pointType);
			pstmt.setString(3, area.id);
			LabelPoint point = null;
			for(rs = pstmt.executeQuery(); rs.next(); points.add(point)) {
				point = new LabelPoint(pointType);
				point.baiduLat = rs.getDouble("Lat");
				point.baiduLng = rs.getDouble("Lng");
				point.id = String.valueOf(rs.getObject("CollectionId"));
				point.name = rs.getString("CollectionName");
				point.seq = rs.getInt("Seq");
			}
		} catch (Exception e) {
			Logger.err(e);
		} finally {
			try {
				if (null != rs)
					rs.close();
				if (null != pstmt)
					pstmt.close();
				if (null != conn)
					conn.close();
			} catch (Exception ex) {
				Logger.err(ex);
			}
		}
		return points;
	}

	/**
	 *  获取垃圾处理场坐标点
	 * @param box
	 * @return
	 */
	public List<LabelPoint> getLabelPoints(HookArmCase box) {
		PooledConnection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<LabelPoint> points = new ArrayList<LabelPoint>();
		String sql = "SELECT CC.CollectionId,CC.CollectionName,CC.Lat,CC.Lng,CC.Seq "
				+"FROM ref_HookArmCase CEA,crc_Collection CC "
				+"WHERE CEA.ProjectId = CC.ProjectId AND CEA.CaseId = ? "
				+"AND (CollectionType = 'D' OR CollectionType = 'E') "
				+"AND CEA.Status = 'Active' AND CC.Status = 'Active' ORDER BY Seq ";

		try {
			conn = DbConnectionManager.getConnection(box.company);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, box.caseId);
			LabelPoint point = null;
			for(rs = pstmt.executeQuery(); rs.next(); points.add(point)) {
				point = new LabelPoint("D;E");
				point.baiduLat = rs.getDouble("Lat");
				point.baiduLng = rs.getDouble("Lng");
				point.id = String.valueOf(rs.getObject("CollectionId"));
				point.name = rs.getString("CollectionName");
				point.seq = rs.getInt("Seq");
			}
		} catch (Exception e) {
			Logger.err(e);
		} finally {
			try {
				if (null != rs)
					rs.close();
				if (null != pstmt)
					pstmt.close();
				if (null != conn)
					conn.close();
			} catch (Exception ex) {
				Logger.err(ex);
			}
		}
		return points;
	}
}
